package icu.zhhll.excel.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * excel工具类
 *
 * @author zh
 * @date 2023/8/20 16:18
 */
public class ExcelUtils {

    /**
     * 设置下拉框
     *
     * @param size
     * @param datas
     * @param sheet
     * @param columnNo
     * @param sheetName 存储下拉框内容的sheet页名称
     * @param sheetNo
     */
    public static void setValidation(int size, List<String> datas, Sheet sheet, int columnNo, String sheetName, int sheetNo) {
        Sheet hiddenSheet = sheet.getWorkbook().createSheet(sheetName);//隐藏的sheet,用于存放下拉框的限定值
        int rowNo = 0;
        for (String data : datas) {
            hiddenSheet.createRow(rowNo++).createCell(0).setCellValue(data);
        }
        setValidation(sheet, sheetName, datas.size(), "A", 1, size, columnNo, columnNo);
        sheet.getWorkbook().setSheetHidden(sheetNo, true);
    }

    /**
     * 设置下拉框
     *
     * @param sheet     模板sheet页（需要设置下拉框的sheet）
     * @param sheetName 隐藏的sheet页，用于存放下拉框的值 （下拉框值对应一列）
     * @param lastRow   存放下拉框值的最后一行
     * @param col       存放下拉框值的列名 "A"
     * @param firstRow  添加下拉框对应开始行
     * @param endRow    添加下拉框对应结束行
     * @param firstCol  添加下拉框对应开始列
     * @param endCol    添加下拉框对应结束列
     */
    private static void setValidation(Sheet sheet, String sheetName, int lastRow, String col, int firstRow,
                                      int endRow, int firstCol, int endCol) {
        // 如果没有数据 给第一行添加下拉框
        if (endRow == 0) {
            endRow = 1;
        }
        //设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        String cell = "\"" + sheetName + "!$" + col + "$1:$" + col + "$" + lastRow + "\"";
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
        // 引用ShtDictionary 的单元格
        DVConstraint constraint = DVConstraint.createFormulaListConstraint("INDIRECT(" + cell + ")");
//        HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
        DataValidation validation = dataValidationHelper.createValidation(constraint, regions);
        sheet.addValidationData(validation);
    }

    /**
     * 插入图片
     *
     * @param workbook
     * @param sheet
     * @param rowNo
     * @param columnNo
     * @param imageName
     */
    public static void createRowToImage(HSSFWorkbook workbook, HSSFSheet sheet, int rowNo, int columnNo, String imageName) {
        BufferedImage bufferImg;//图片

        try (// 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
             ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
             //将图片读到BufferedImage
             InputStream resource = Thread.currentThread().getContextClassLoader().getResourceAsStream(imageName)) {

            if (resource == null) {
                throw new RuntimeException(imageName + "资源不存在");
            }
            bufferImg = ImageIO.read(resource);
            // 将图片写入流中
            ImageIO.write(bufferImg, "png", byteArrayOut);

            // 利用HSSFPatriarch将图片写入EXCEL
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

            HSSFCreationHelper creationHelper = workbook.getCreationHelper();
            HSSFClientAnchor clientAnchor = creationHelper.createClientAnchor();
            clientAnchor.setCol1(columnNo);
            clientAnchor.setRow1(rowNo);


            // 插入图片
            HSSFPicture picture = patriarch.createPicture(clientAnchor, workbook.addPicture(byteArrayOut
                    .toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
            picture.resize(2.5);

        } catch (IOException e) {
            throw new RuntimeException("插入失败", e);
        }

    }
}
